﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;




namespace EmployeeCheckIn
{
    public partial class ShowList_Empoyee : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["User"] != null && Session["Pass"] != null)
            {
                if (!IsPostBack)
                {
                    TextBox_date_sert1.Text = "";
                    TextBox_date_sert2.Text = "";
                    TextBox_insert.Text = "";
                    bindingGrid();

                }
            }
            else
            {
                Response.Redirect("Default.aspx");
            }
        }

           
        private DataTable GetData()
        {
            string sqltext = @"SELECT EMP_CODE,EMP_FIRSTNAME,EMP_LASTNAME,TBL_POSITION.POSITION_NAME,EMP_START_DATE 
                              ,TBL_STATUS.EMP_STATUS_NAME FROM (EMPLOYEE inner join TBL_POSITION ON EMPLOYEE.EMP_POSITION_ID = TBL_POSITION.POSITION_ID)
                               inner join TBL_STATUS ON EMPLOYEE.EMP_STATUS_ID = TBL_STATUS.EMP_STATUS_ID ORDER BY EMP_CODE ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetData(string i)
        {
            string sqltext = @"Select * From EMPLOYEE WHERE EMP_CODE = '"+i+"'";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private void bindinggridShowdata(string i)
        {
            DataTable dt = GetData(i);
            if (dt !=null && dt.Rows.Count>0)
            {
                Session["Showdata"] = dt;
                Response.Redirect("ShowDataEmp.aspx");//หน้าของซันนี่แสดงข้อมูล
            }
 
        }
        private void bindinggridEditdata(string i)
        {
            DataTable dt = GetData(i);
            if (dt != null && dt.Rows.Count > 0)
            {
                Session["Editdata"] = dt;
                Response.Redirect("EditDataEmp.aspx");//หน้าของซันนี่แสดงข้อมูล
            }

        }
        private void bindingGrid()
        {
            DataTable dt = GetData();
            if (dt.Rows.Count == 0)
            {
                Label_Nodata.Visible = true;
                Label_Nodata.Text = "----------ไม่พบข้อมูลการค้นหา----------";
            }
            else
            {
                Label_Nodata.Visible = false;
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
        private DataTable GetData_sertall(string i , string s,string j ,string k)
        {
            string sqltext = @"SELECT EMP_CODE,EMP_FIRSTNAME,EMP_LASTNAME,TBL_POSITION.POSITION_NAME,EMP_START_DATE,TBL_STATUS.EMP_STATUS_NAME 
                            FROM (EMPLOYEE inner join TBL_POSITION ON EMPLOYEE.EMP_POSITION_ID = TBL_POSITION.POSITION_ID) inner join TBL_STATUS ON EMPLOYEE.EMP_STATUS_ID = TBL_STATUS.EMP_STATUS_ID
                             WHERE (EMP_START_DATE BETWEEN '" + i + "' AND '" + s + "') AND (EMP_CODE Like ('"+j+"%')OR EMP_FIRSTNAME Like ('"+k+"%')) ORDER BY EMP_CODE";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetData_sertCodeName(string i, string s)
        {
            string sqltext = @"SELECT EMP_CODE,EMP_FIRSTNAME,EMP_LASTNAME,TBL_POSITION.POSITION_NAME, EMP_START_DATE,TBL_STATUS.EMP_STATUS_NAME 
                            FROM (EMPLOYEE inner join TBL_POSITION ON EMPLOYEE.EMP_POSITION_ID = TBL_POSITION.POSITION_ID) inner join TBL_STATUS ON EMPLOYEE.EMP_STATUS_ID = TBL_STATUS.EMP_STATUS_ID
                             WHERE EMP_CODE LIKE ('" + i + "%') OR EMP_FIRSTNAME Like ('" + s + "%') ORDER BY EMP_CODE";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetData_sertCalendar(string i, string s)
        {
            string sqltext = @"SELECT EMP_CODE,EMP_FIRSTNAME,EMP_LASTNAME,TBL_POSITION.POSITION_NAME,EMP_START_DATE,TBL_STATUS.EMP_STATUS_NAME 
                            FROM (EMPLOYEE inner join TBL_POSITION ON EMPLOYEE.EMP_POSITION_ID = TBL_POSITION.POSITION_ID) inner join TBL_STATUS ON EMPLOYEE.EMP_STATUS_ID = TBL_STATUS.EMP_STATUS_ID
                             WHERE EMP_START_DATE BETWEEN '" + s + "' AND '" + i + "' ORDER BY EMP_START_DATE";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
      
       


       
       
        public override void VerifyRenderingInServerForm(Control control) { }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (Session["User"] != null && Session["Pass"] != null)
            {
                if (e.CommandName == "cmdEdit")
                {
                    string i = e.CommandArgument.ToString();
                    bindinggridEditdata(i);
                }
                if (e.CommandName == "cmdDelete")
                {
                    string i = e.CommandArgument.ToString();
                    DeleteData(i);
                    bindingGrid();
                }
                if (e.CommandName == "cmdShowdata")
                {
                    string i = e.CommandArgument.ToString();
                    bindinggridShowdata(i);
                }
                if (e.CommandName == "cmdShowtime")
                {
                    string Emp_code = e.CommandArgument.ToString();
                    Session["Emp_code"] = Emp_code;
                    Response.Redirect("ShowTime_Empoyee.aspx");
                }
            }
            else
            {
                Response.Redirect("Default.aspx");
            }
        }
        private void DeleteData(string i)
        {
            string sqltext = "Delete EMPLOYEE WHERE EMP_CODE = (@emp_CODE)";
            SqlCommand sqlcmm = new SqlCommand();
            SqlConnection sqlcnn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            sqlcmm.Connection = sqlcnn;
            sqlcmm.CommandType = System.Data.CommandType.Text;
            sqlcmm.CommandText = sqltext;
            sqlcmm.Parameters.AddWithValue("@emp_CODE", i);
            try
            {
                sqlcmm.Connection.Open();
                sqlcmm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcmm.Connection.Close();
                sqlcmm.Connection.Dispose();
                
                
            }
            sqlcmm.Connection.Close();
            sqlcmm.Connection.Dispose();
            Response.Write("<script>alert('ลบข้อมูลเรียบร้อยแล้ว!'); window.location='ShowList_Empoyee.aspx';</script>");
        }

       

        protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes["onmouseover"] = "this.style.cursor='hand';this.style.backgroundColor='#6699FF';";
                e.Row.Attributes["onmouseout"] = "this.style.cursor='hand';this.style.backgroundColor='white';";

               // e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "Select$" + e.Row.RowIndex);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
          if (Session["User"] != null && Session["Pass"] != null)
            {

                if ((TextBox_date_sert1.Text != "") && (TextBox_date_sert2.Text != "") && (TextBox_insert.Text != ""))
                {
                    string date_sert1 = Convert.ToDateTime(TextBox_date_sert1.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                    string date_sert2 = Convert.ToDateTime(TextBox_date_sert2.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                    string empcode = TextBox_insert.Text;
                    DataTable dt = GetData_sertall(date_sert1, date_sert2, empcode, TextBox_insert.Text);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (GridView1.Rows.Count == 0)
                    {
                        GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- ไม่พบข้อมูลที่ค้นหา กรุณาลองใหม่อีกครั้ง ------";
                    }
                    else
                    {
                        GridView1.Visible = true;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "-----พบข้อมูลที่ค้นหาทั้งหมด " + GridView1.Rows.Count + " รายการ-----";
                    }

                }
                if ((TextBox_date_sert1.Text == "") && (TextBox_date_sert2.Text == "") && (TextBox_insert.Text != ""))
                {
                    string empcode = TextBox_insert.Text;
                    DataTable dt = GetData_sertCodeName(empcode, TextBox_insert.Text);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (GridView1.Rows.Count == 0)
                    {
                        GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- ไม่พบข้อมูลที่ค้นหา กรุณาลองใหม่อีกครั้ง ------";
                    }
                    else
                    {
                        GridView1.Visible = true;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "-----พบข้อมูลที่ค้นหาทั้งหมด " + GridView1.Rows.Count + " รายการ-----";
                    }

                }
                if ((TextBox_date_sert1.Text == "") && (TextBox_date_sert2.Text == "") && (TextBox_insert.Text == ""))
                {

                    //Response.Write(@"<script language='javascript'>alert('กรุณาใส่ข้อมูลเพื่อค้นหา')</script>");
                    Label_Nodata.Visible = false;
                    GridView1.Visible = true;
                    bindingGrid();
                }
                if ((TextBox_date_sert1.Text != "") && (TextBox_date_sert2.Text != "") && (TextBox_insert.Text == ""))
                {
                    string date_sert1 = Convert.ToDateTime(TextBox_date_sert1.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                    string date_sert2 = Convert.ToDateTime(TextBox_date_sert2.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                    DataTable dt = GetData_sertCalendar(date_sert1, date_sert2);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (GridView1.Rows.Count == 0)
                    {
                        GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- ไม่พบข้อมูลที่ค้นหา กรุณาลองใหม่อีกครั้ง ------";
                    }
                    else
                    {
                        GridView1.Visible = true;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "-----พบข้อมูลที่ค้นหาทั้งหมด " + GridView1.Rows.Count + " รายการ-----";
                    }
                }

                if (TextBox_date_sert1.Text == "" && TextBox_date_sert2.Text != "" && TextBox_insert.Text != "")
                {
                    GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- กรุณากรอกวันเริ่มต้นและสิ้นสุด ------";
             

                }
                if (TextBox_date_sert1.Text != "" && TextBox_date_sert2.Text == "" && TextBox_insert.Text != "")
                {
                    GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- กรุณากรอกวันเริ่มต้นและสิ้นสุด ------";
                  

                }
                if (TextBox_date_sert1.Text == "" && TextBox_date_sert2.Text != "" && TextBox_insert.Text == "")
                {
                        GridView1.Visible = false;
                        Label_Nodata.Visible = true;
                        Label_Nodata.Text = "----- กรุณากรอกวันเริ่มต้นและสิ้นสุด ------";
                  

                }
                if (TextBox_date_sert1.Text != "" && TextBox_date_sert2.Text == "" && TextBox_insert.Text == "")
                {
                    GridView1.Visible = false;
                    Label_Nodata.Visible = true;
                    Label_Nodata.Text = "----- กรุณากรอกวันเริ่มต้นและสิ้นสุด ------";
                }

            }
            else
            {
                Response.Redirect("Default.aspx");
            }

        }

        protected void Add_emp_Click(object sender, EventArgs e)
        {
             
            if (Session["User"] != null && Session["Pass"] != null)
            {
                Response.Redirect("RegisterPage.aspx");
            }
            else
            {
                Response.Redirect("Default.aspx");
            }
        
        }

        protected void Bt_export_Click(object sender, EventArgs e)
        {
            
            if (Session["User"] != null && Session["Pass"] != null)
            {
                if (GridView1.Rows.Count != 0)
                {
                    if ((TextBox_date_sert1.Text != "") && (TextBox_date_sert2.Text != "") && (TextBox_insert.Text != ""))
                    {
                       
                            string date_sert1 = Convert.ToDateTime(TextBox_date_sert1.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                            string date_sert2 = Convert.ToDateTime(TextBox_date_sert2.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                            string empcode = TextBox_insert.Text;
                            DataTable dt = GetData_sertall(date_sert1, date_sert2, empcode, TextBox_insert.Text);
                            Response.Clear();
                            Response.Buffer = true;
                            Response.AddHeader("content-disposition", "attachment;filename=ExportEmp.xls");
                            this.EnableViewState = false;
                            Response.Charset = string.Empty;
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            StringWriter sw = new StringWriter();
                            HtmlTextWriter hw = new HtmlTextWriter(sw);
                            GridView1.AllowPaging = false;
                            GridView1.AllowSorting = false;
                            GridView1.Columns[GridView1.HeaderRow.Cells.Count - 1].Visible = false;
                            GridView1.DataSource = dt;
                            GridView1.DataBind();

                            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

                            for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                            {
                                GridView1.HeaderRow.Cells[k].Style.Add("background-color", "#006699");

                            }
                            for (int i = 0; i < GridView1.Rows.Count; i++)
                            {

                                GridViewRow row = GridView1.Rows[i];

                                //Change Color back to white

                                row.BackColor = System.Drawing.Color.White;

                                //Apply text style to each Row

                                row.Attributes.Add("class", "textmode");
                                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                                {
                                    if (i % 2 != 0)
                                    {
                                        row.Cells[j].Style.Add("background-color", "#0099FF");

                                    }

                                }



                            }

                            GridView1.RenderControl(hw);

                            //style to format numbers to string

                            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                            Response.Write(style);
                            Response.Output.Write(sw.ToString());
                            Response.Flush();
                            Response.End();

                        }
                       
                    
                    if ((TextBox_date_sert1.Text == "") && (TextBox_date_sert2.Text == "") && (TextBox_insert.Text != ""))
                    {
                       
                            string empcode = TextBox_insert.Text;
                            DataTable dt = GetData_sertCodeName(empcode, TextBox_insert.Text);
                            Response.Clear();
                            Response.Buffer = true;
                            Response.AddHeader("content-disposition", "attachment;filename=ExportEmp.xls");
                            this.EnableViewState = false;
                            Response.Charset = string.Empty;
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            StringWriter sw = new StringWriter();
                            HtmlTextWriter hw = new HtmlTextWriter(sw);
                            GridView1.AllowPaging = false;
                            GridView1.AllowSorting = false;
                            GridView1.Columns[GridView1.HeaderRow.Cells.Count - 1].Visible = false;
                            GridView1.DataSource = dt;
                            GridView1.DataBind();

                            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

                            for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                            {
                                GridView1.HeaderRow.Cells[k].Style.Add("background-color", "#006699");

                            }
                            for (int i = 0; i < GridView1.Rows.Count; i++)
                            {

                                GridViewRow row = GridView1.Rows[i];

                                //Change Color back to white

                                row.BackColor = System.Drawing.Color.White;

                                //Apply text style to each Row

                                row.Attributes.Add("class", "textmode");
                                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                                {
                                    if (i % 2 != 0)
                                    {
                                        row.Cells[j].Style.Add("background-color", "#0099FF");

                                    }

                                }



                            }

                            GridView1.RenderControl(hw);

                            //style to format numbers to string

                            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                            Response.Write(style);
                            Response.Output.Write(sw.ToString());
                            Response.Flush();
                            Response.End();
                        }
                       
                    
                    if ((TextBox_date_sert1.Text != "") && (TextBox_date_sert2.Text != "") && (TextBox_insert.Text == ""))
                    {
                       
                            string date_sert1 = Convert.ToDateTime(TextBox_date_sert1.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                            string date_sert2 = Convert.ToDateTime(TextBox_date_sert2.Text).ToString("yyyy/MM/dd", new System.Globalization.CultureInfo("en-US"));
                            DataTable dt = GetData_sertCalendar(date_sert1, date_sert2);
                            Response.Clear();
                            Response.Buffer = true;
                            Response.AddHeader("content-disposition", "attachment;filename=ExportEmp.xls");
                            this.EnableViewState = false;
                            Response.Charset = string.Empty;
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            StringWriter sw = new StringWriter();
                            HtmlTextWriter hw = new HtmlTextWriter(sw);
                            GridView1.AllowPaging = false;
                            GridView1.AllowSorting = false;
                            GridView1.Columns[GridView1.HeaderRow.Cells.Count - 1].Visible = false;
                            GridView1.DataSource = dt;
                            GridView1.DataBind();

                            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

                            for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                            {
                                GridView1.HeaderRow.Cells[k].Style.Add("background-color", "#006699");

                            }
                            for (int i = 0; i < GridView1.Rows.Count; i++)
                            {

                                GridViewRow row = GridView1.Rows[i];

                                //Change Color back to white

                                row.BackColor = System.Drawing.Color.White;

                                //Apply text style to each Row

                                row.Attributes.Add("class", "textmode");
                                for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                                {
                                    if (i % 2 != 0)
                                    {
                                        row.Cells[j].Style.Add("background-color", "#0099FF");

                                    }

                                }



                            }

                            GridView1.RenderControl(hw);

                            //style to format numbers to string

                            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                            Response.Write(style);
                            Response.Output.Write(sw.ToString());
                            Response.Flush();
                            Response.End();
                       
                    }
                    else
                    {
                        DataTable dt = GetData();
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment;filename=ExportEmp.xls");
                        this.EnableViewState = false;
                        Response.Charset = string.Empty;
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        StringWriter sw = new StringWriter();
                        HtmlTextWriter hw = new HtmlTextWriter(sw);
                        GridView1.AllowPaging = false;
                        GridView1.AllowSorting = false;
                        GridView1.Columns[GridView1.HeaderRow.Cells.Count - 1].Visible = false;
                        GridView1.DataSource = dt;
                        GridView1.DataBind();

                        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");


                        for (int k = 0; k < GridView1.HeaderRow.Cells.Count; k++)
                        {
                            GridView1.HeaderRow.Cells[k].Style.Add("background-color", "#006699");


                        }
                        for (int i = 0; i < GridView1.Rows.Count; i++)
                        {

                            GridViewRow row = GridView1.Rows[i];

                            //Change Color back to white

                            row.BackColor = System.Drawing.Color.White;

                            //Apply text style to each Row

                            row.Attributes.Add("class", "textmode");
                            for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
                            {
                                if (i % 2 != 0)
                                {
                                    row.Cells[j].Style.Add("background-color", "#6699FF");


                                }

                            }



                        }

                        GridView1.RenderControl(hw);

                        //style to format numbers to string

                        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                        Response.Write(style);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.End();
                    }
                }
                else
                {
                    Response.Write(@"<script language='javascript'>alert('ไม่มีข้อมูลที่จะทำการ Export')</script>");
                   
                }
            }
            else
            {
                Response.Redirect("Default.aspx");
            }

        
        }

         

           

   
        


    }

        }
    
